package com.example.redislearning.mapper;

import com.example.redislearning.entity.UserCourse;
import com.example.redislearning.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.math.BigDecimal;

/**
 * 用户课程购买记录数据访问层
 * MyBatis Mapper接口
 */
@Mapper
public interface UserCourseMapper {

    /**
     * 插入用户课程购买记录
     */
    @Insert("INSERT INTO user_courses (user_id, course_id, purchase_price, progress_percent, total_study_time, status) " +
            "VALUES (#{userId}, #{courseId}, #{purchasePrice}, #{progressPercent}, #{totalStudyTime}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(UserCourse userCourse);

    /**
     * 根据用户ID和课程ID查询购买记录
     */
    @Select("SELECT * FROM user_courses WHERE user_id = #{userId} AND course_id = #{courseId}")
    UserCourse findByUserIdAndCourseId(@Param("userId") Integer userId, @Param("courseId") Integer courseId);

    /**
     * 根据用户ID和课程ID查询购买记录（简化版）
     */
    @Select("SELECT * FROM user_courses WHERE user_id = #{userId} AND course_id = #{courseId}")
    UserCourse selectByUserAndCourse(@Param("userId") Integer userId, @Param("courseId") Integer courseId);

    /**
     * 根据用户ID查询购买记录
     */
    @Select("SELECT * FROM user_courses WHERE user_id = #{userId}")
    List<UserCourse> findByUserId(Integer userId);

    /**
     * 查询用户已完成的课程
     */
    @Select("SELECT * FROM user_courses WHERE user_id = #{userId} AND status = 2")
    List<UserCourse> findCompletedByUserId(Integer userId);

    /**
     * 获取用户总学习时长
     */
    @Select("SELECT COALESCE(SUM(study_time), 0) FROM user_courses WHERE user_id = #{userId}")
    Integer getTotalStudyTimeByUserId(Integer userId);

    /**
     * 查询用户的学习课程列表
     */
    @Select("SELECT uc.*, c.* FROM user_courses uc " +
            "JOIN courses c ON uc.course_id = c.id " +
            "WHERE uc.user_id = #{userId} ORDER BY uc.purchase_time DESC")
    @Results({
        @Result(column = "id", property = "id", id = true),
        @Result(column = "user_id", property = "userId"),
        @Result(column = "course_id", property = "courseId"),
        @Result(column = "purchase_price", property = "purchasePrice"),
        @Result(column = "progress_percent", property = "progressPercent"),
        @Result(column = "total_study_time", property = "totalStudyTime"),
        @Result(column = "status", property = "status"),
        @Result(column = "purchase_time", property = "purchaseTime"),
        @Result(column = "last_study_time", property = "lastStudyTime"),
        @Result(column = "created_at", property = "createdAt"),
        @Result(column = "updated_at", property = "updatedAt"),
        @Result(column = "id", property = "course.id"),
        @Result(column = "title", property = "course.title"),
        @Result(column = "instructor", property = "course.instructor"),
        @Result(column = "price", property = "course.price"),
        @Result(column = "cover_image", property = "course.coverImage"),
        @Result(column = "duration", property = "course.duration")
    })
    List<UserCourse> selectByUserId(Integer userId);

    /**
     * 更新学习进度
     */
    @Update("UPDATE user_courses SET progress_percent = #{progress}, study_time = #{studyTime}, " +
            "last_study_time = NOW(), updated_at = NOW(), status = #{status}, completed_at = #{completedAt} " +
            "WHERE user_id = #{userId} AND course_id = #{courseId}")
    int updateProgress(UserCourse userCourse);

    /**
     * 更新学习进度（简化版）
     */
    @Update("UPDATE user_courses SET progress_percent = #{progressPercent}, total_study_time = #{totalStudyTime}, " +
            "last_study_time = NOW(), updated_at = NOW() WHERE user_id = #{userId} AND course_id = #{courseId}")
    int updateProgress(@Param("userId") Integer userId, @Param("courseId") Integer courseId, 
                      @Param("progressPercent") BigDecimal progressPercent, @Param("totalStudyTime") Integer totalStudyTime);

    /**
     * 标记课程完成
     */
    @Update("UPDATE user_courses SET status = 2, progress_percent = 100, updated_at = NOW() " +
            "WHERE user_id = #{userId} AND course_id = #{courseId}")
    int markCourseCompleted(@Param("userId") Integer userId, @Param("courseId") Integer courseId);

    /**
     * 查询用户学习的课程数量
     */
    @Select("SELECT COUNT(*) FROM user_courses WHERE user_id = #{userId}")
    int countUserCourses(Integer userId);

    /**
     * 查询用户已完成的课程数量
     */
    @Select("SELECT COUNT(*) FROM user_courses WHERE user_id = #{userId} AND status = 2")
    int countCompletedCourses(Integer userId);

    /**
     * 查询课程的学生数量
     */
    @Select("SELECT COUNT(*) FROM user_courses WHERE course_id = #{courseId}")
    int countCourseStudents(Integer courseId);

    /**
     * 查询用户是否购买了课程
     */
    @Select("SELECT COUNT(*) FROM user_courses WHERE user_id = #{userId} AND course_id = #{courseId}")
    int checkUserPurchased(@Param("userId") Integer userId, @Param("courseId") Integer courseId);

    /**
     * 查询学习排行榜
     */
    @Select("SELECT u.id, u.username, u.nickname, SUM(uc.total_study_time) as total_study_time " +
            "FROM users u JOIN user_courses uc ON u.id = uc.user_id " +
            "WHERE u.status = 1 GROUP BY u.id ORDER BY total_study_time DESC LIMIT #{limit}")
    @Results({
        @Result(column = "id", property = "id"),
        @Result(column = "username", property = "username"),
        @Result(column = "nickname", property = "nickname"),
        @Result(column = "total_study_time", property = "totalStudyTime")
    })
    List<User> selectStudyLeaderboard(@Param("limit") int limit);
}